<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>shell 备份 mysql | 失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		<article>
	
		<h1>shell 备份 mysql</h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/shell/">shell</a>
			</span>
		
	</div>

	

	
		<ol>
<li>创建备份目录和文件</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mkdir/srv/bakmysql</div><div class="line">mkdir/srv/bakmysql/daily</div><div class="line">touch/srv/bakmysql/mysqlbak.log</div></pre></td></tr></table></figure>
<ol>
<li>启用二进制日志</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">cd</span> /etc/</div><div class="line">vi my.cnf</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在[mysqld]添加</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">log</span>-bin=/var/lib/mysql/mysql-bin.log</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后重启mysqld服务器</p>
<ol>
<li>设置crontab任务，每天执行备份脚本</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">cd</span> /etc/</div><div class="line">vi crontab</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;添加如下脚本</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">0 0 * * 0 root/usr/sbin/mysqlfullbak     <span class="comment">#（每天0点执行完全备份脚本）</span></div><div class="line">0 1 * * * root/usr/sbin/mysqldailybak    <span class="comment"># (每天凌晨1点执行增量备份脚本)</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;脚本内容：</p>
<ol>
<li>完全备份脚本</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div></pre></td><td class="code"><pre><div class="line"><span class="meta">#!/bin/bash</span></div><div class="line"><span class="comment"># Name:mysqlfullbak</span></div><div class="line"><span class="comment"># This is mysql mysqlfullbak scripts</span></div><div class="line"><span class="comment"># By Cloudsoar</span></div><div class="line"><span class="comment"># Last Modify:2012-05-10</span></div><div class="line"><span class="comment">#定义脚本存放路径</span></div><div class="line">scriptsDir=/usr/sbin</div><div class="line"><span class="comment">#定义用户名及密码</span></div><div class="line">user=root</div><div class="line">userPWD=9dspod8a</div><div class="line"><span class="comment">#定义要备份的数据库</span></div><div class="line">database=GoToMyCloudDB</div><div class="line"><span class="comment">#定义完全备份文件存放路径</span></div><div class="line">bakDir=/srv/bakmysql</div><div class="line">eMailFile=<span class="variable">$bakDir</span>/email.txt</div><div class="line">eMail=xia.yan@cloudsoar.com</div><div class="line"><span class="comment">#定义日志文件</span></div><div class="line">LogFile=<span class="variable">$bakDir</span>/mysqlbak.log</div><div class="line">DATE=`date +%Y%m%d`</div><div class="line"><span class="built_in">echo</span> <span class="string">" "</span> &gt;&gt; <span class="variable">$LogFile</span></div><div class="line"><span class="built_in">echo</span> <span class="string">" "</span> &gt;&gt; <span class="variable">$LogFile</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"--------------------------"</span> &gt;&gt; <span class="variable">$LogFile</span></div><div class="line"><span class="built_in">echo</span> $(date +<span class="string">"%y-%m-%d %H:%M:%S"</span>) &gt;&gt;<span class="variable">$LogFile</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"-----------------"</span> &gt;&gt; <span class="variable">$LogFile</span></div><div class="line"><span class="built_in">cd</span> <span class="variable">$bakDir</span></div><div class="line">DumpFile=<span class="variable">$DATE</span>.sql</div><div class="line">mysqldump --flush-logs -u<span class="variable">$user</span> -p<span class="variable">$userPWD</span> --quick <span class="variable">$database</span> &gt;<span class="variable">$DumpFile</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"Dump Done"</span> &gt;&gt; <span class="variable">$LogFile</span></div><div class="line"><span class="built_in">echo</span>  <span class="string">"[<span class="variable">$DumpFile</span>]Backup Success!"</span> &gt;&gt; <span class="variable">$LogFile</span></div><div class="line">daily_databakDir=<span class="variable">$bakDir</span>/daily</div><div class="line"><span class="built_in">cd</span> <span class="variable">$bakDir</span>/daily</div><div class="line">find <span class="variable">$daily_databakDir</span> -name <span class="string">"daily*"</span> -<span class="built_in">type</span> f -mtime +35 -<span class="built_in">exec</span> rm &#123;&#125; \; &gt; /dev/null 2&gt;&amp;1</div></pre></td></tr></table></figure>
<ol>
<li>增量备份脚本</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div></pre></td><td class="code"><pre><div class="line"><span class="meta">#!/bin/bash</span></div><div class="line"><span class="comment"># Name:mysqldailybak</span></div><div class="line"><span class="comment"># This ia mysql mysqldailybak scripts</span></div><div class="line"><span class="comment"># By Cloudsoar</span></div><div class="line"><span class="comment"># Last modify:2012-05-10</span></div><div class="line"><span class="comment">#定义用户名及密码</span></div><div class="line">user=root</div><div class="line">userPWD=9dspod8a</div><div class="line"><span class="comment">#定义数据库</span></div><div class="line">database=GotoMyCloudDB</div><div class="line">/usr/bin/mysqladmin -u<span class="variable">$user</span> -p<span class="variable">$userPWD</span> flush-logs</div><div class="line">daily_databakDir=/srv/bakmysql/daily</div><div class="line"><span class="comment">#定义MYSQL数据目录</span></div><div class="line">mysqlDataDir=/srv/mysql</div><div class="line">eMailFile=<span class="variable">$daily_databakDir</span>/email.txt</div><div class="line">eMail=xia.yan@cloudsoar.com</div><div class="line">DATE=`date +%Y%m%d`</div><div class="line">logFile=<span class="variable">$daily_databakDir</span>/mysql<span class="variable">$DATE</span>.<span class="built_in">log</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"       "</span> &gt; <span class="variable">$eMailFile</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"-----------------------"</span> &gt;&gt; <span class="variable">$eMailFile</span> </div><div class="line"><span class="built_in">echo</span> $(date +<span class="string">"%y-%m-%d %H:%M:%S"</span>) &gt;&gt; <span class="variable">$eMailFile</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"-------</span></div><div class="line"><span class="string">------------------"</span> &gt;&gt; <span class="variable">$eMailFile</span></div><div class="line">TIME=$(date <span class="string">"-d 10 day ago"</span> +%Y%m%d%H%M%S)</div><div class="line">StartTime=$(date <span class="string">"-d 1 day ago"</span> +<span class="string">"%Y-%m-%d %H:%M:%S"</span>)</div><div class="line"><span class="built_in">echo</span> “Delete 10 days before the <span class="built_in">log</span>” &gt;&gt;<span class="variable">$eMailFile</span></div><div class="line">mysql -u<span class="variable">$user</span> -p<span class="variable">$userPWD</span> -e <span class="string">"purge master logs before <span class="variable">$&#123;TIME&#125;</span>"</span> &amp;&amp; <span class="built_in">echo</span> <span class="string">"delete 10 days before log"</span> |tee -a <span class="variable">$eMailFile</span> <span class="comment">#删除10天前的2进制文件</span></div><div class="line">filename=`cat <span class="variable">$mysqlDataDir</span>/mysqld-bin.index |awk -F <span class="string">"/"</span> <span class="string">'&#123;print $2&#125;'</span>` <span class="comment"># 2进制文件</span></div><div class="line"><span class="keyword">for</span> i <span class="keyword">in</span> <span class="variable">$filename</span></div><div class="line"><span class="keyword">do</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"<span class="variable">$StartTime</span> start backup binlog"</span> &gt;&gt; <span class="variable">$eMailFile</span></div><div class="line">mysqlbinlog -u<span class="variable">$user</span> -p<span class="variable">$userPWD</span> -d <span class="variable">$database</span> --start-datetime=<span class="string">"<span class="variable">$StartTime</span>"</span> <span class="variable">$mysqlDataDir</span>/<span class="variable">$i</span> &gt;&gt; <span class="variable">$daily_databakDir</span>/daily<span class="variable">$DATE</span>.sql |tee -a <span class="variable">$eMailFile</span></div><div class="line"><span class="keyword">done</span></div><div class="line"><span class="keyword">if</span> [ $? = 0 ]</div><div class="line"><span class="keyword">then</span></div><div class="line"><span class="comment"># 删除mtime&gt;32的增量日志备份文件</span></div><div class="line">find <span class="variable">$daily_databakDir</span> -name <span class="string">"*.log"</span> -<span class="built_in">type</span> f -mtime +32 -<span class="built_in">exec</span> rm &#123;&#125; \; &gt; /dev/null 2&gt;&amp;1</div><div class="line"><span class="built_in">cd</span> <span class="variable">$daily_databakDir</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"Daily backup succeed"</span> &gt;&gt; <span class="variable">$eMailFile</span></div><div class="line"><span class="keyword">else</span></div><div class="line"><span class="built_in">echo</span> <span class="string">"Daily backup fail"</span> &gt;&gt; <span class="variable">$eMailFile</span></div><div class="line">mail -s <span class="string">"MySQL Backup"</span> <span class="variable">$eMail</span> &lt; <span class="variable">$eMailFile</span> <span class="comment">#备份失败之后发送邮件通知</span></div><div class="line"><span class="keyword">fi</span></div><div class="line">cat <span class="variable">$eMailFile</span> &gt; <span class="variable">$logFile</span></div><div class="line"><span class="comment"># 删除mtime&gt;32的增量日志备份文件</span></div><div class="line">find <span class="variable">$daily_databakDir</span> -name <span class="string">"*.log"</span> -<span class="built_in">type</span> f -mtime +32 -<span class="built_in">exec</span> rm &#123;&#125; \; &gt; /dev/null 2&gt;&amp;</div></pre></td></tr></table></figure>
<ol>
<li>删除完全备份脚本</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line"><span class="meta">#!/bin/sh</span></div><div class="line"><span class="comment"># Name:rmBackup</span></div><div class="line"><span class="comment"># PS:Delete old Backup.</span></div><div class="line"><span class="comment"># By:Cloudsoar</span></div><div class="line"><span class="comment"># Last Modify:2012-05-10</span></div><div class="line"><span class="comment"># 定义备份目录</span></div><div class="line">dataBackupDir=/srv/bakmysql</div><div class="line"><span class="comment"># 删除mtime&gt;32的日志备份文件</span></div><div class="line">find <span class="variable">$dataBackupDir</span> -name <span class="string">"*.sql"</span> -<span class="built_in">type</span> f -mtime +32 -<span class="built_in">exec</span> rm &#123;&#125; \; &gt; /dev/null 2&gt;&amp;1</div></pre></td></tr></table></figure>
<ol>
<li>添加可执行权限</li>
</ol>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">chmod +x mysqlfullbak</div><div class="line">chmod +x mysqldailybak</div><div class="line">chmod +x rmBackup</div></pre></td></tr></table></figure>

	

	
		<span class="different-posts"><a href="/2017/10/12/Shell/5. shell 备份 mysql/" onclick="window.history.go(-1); return false;">⬅️ Go back </a></span>

	

</article>

	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
